# coding=utf-8

from bs4 import BeautifulSoup  # 网页解析，获取数据
import re  # 正则表达式，进行文字匹配
import urllib.request  # 制定url，获取网页数据
import urllib.error
import xlwt  # 进行Excel操纵
import sqlite3  # 进行数据库操作

# 创建正则表达式对象，表示规则--re.S让换行符包括在字符中--(.*)提取的是括号里的内容
findLink = re.compile(r'<a href="(.*)">')  # 影片链接
findImgSrc = re.compile(r'<img.*src="(.*?)"', re.S)  # 图片链接
findTitle = re.compile(r'<span class="title">(.*)</span>')  # 影片标题
findRating = re.compile(r'<span class="rating_num" property="v:average">(.*)</span>')  # 影片评分
findJudge = re.compile(r'<span>(\d*)人评价</span>')  # 评价人数
findInfo = re.compile(r'<span class="inq">(.*)</span>')  # 找到概况
findBd = re.compile(r'<p class="">(.*?)</p>', re.S)  # 找到影片的相关内容


def main():
    baseUrl = "https://movie.douban.com/top250?start="
    datalist = getData(baseUrl)  # --调用进行爬取(需要保存的数据)
    # 3.保存到Excle表格
    # savePath="豆瓣电影top250.xls"
    # saveData(datalist,savePath)
    # 3.1保存到数据库
    dbPath = "movieTop250.db"
    saveDB(datalist, dbPath)


# 1.爬取网页
def getData(baseUrl):
    datalist = []
    # 爬取多次
    for i in range(0, 10):
        url = baseUrl + str(i * 25)
        html = askURL(url)
        # 2.逐一解析数据
        soup = BeautifulSoup(html, "html.parser")
        for item in soup.find_all('div', class_="item"):  # 查找符合要求的字符串形成列表
            # print(item)
            data = []  # 保存每一部电影的所有信息
            item = str(item)  # 转换为字符串并通过正则表达式进行提取
            link = re.findall(findLink, item)[0]  # 影片链接
            data.append(link)
            imageSrc = re.findall(findImgSrc, item)[0]  # 图片链接
            data.append(imageSrc)
            titles = re.findall(findTitle, item)  # 影片标题
            if (len(titles) == 2):
                ctitle = titles[0]  # 添加中文名
                data.append(ctitle)
                otitle = titles[1].replace("/", "")  # 添加外国名
                data.append(otitle)
            else:
                data.append(titles[0])
                data.append(' ')  # 外国名留空占位
            rating = re.findall(findRating, item)[0]  # 影片评分
            data.append(rating)
            judge = re.findall(findJudge, item)[0]  # 评价人数
            data.append(judge)
            info = re.findall(findInfo, item)  # 找到概况
            if len(info) != 0:
                info = info[0].replace('。', "")
                data.append(info)
            else:
                data.append(" ")
            bd = re.findall(findBd, item)[0]  # 找到影片的相关内容
            bd = re.sub('<br(\s+)?/>(\s+)?', " ", bd)  # \s匹配空白和tab键
            data.append(bd.strip())
            datalist.append(data)
    # print(datalist)
    return datalist


# 3.保存数据为excle表格
def saveData(datalist, savePath):
    book = xlwt.Workbook(encoding='utf-8')
    sheet = book.add_sheet('豆瓣电影Top250')
    col = ("影片链接", "图片链接", "影片中文名", "影片外国名", "影片评分", "评价人数", "找到概况", "影片的相关内容")
    for i in range(0, 8):
        sheet.write(0, i, col[i])  # 列名
    for i in range(0, 250):
        print("第%d条" % (i + 1))
        data = datalist[i]
        # 保存数据
        for j in range(0, 8):
            sheet.write(i + 1, j, data[j])
    # 保存到xls文件中
    book.save(savePath)


# 3.1 保存数据到SQLite数据库
def saveDB(datalist, dbPath):
    sql = '''
        create table movie250
        (
            id integer primary key autoincrement,
            movieLink text,
            imgLink text,
            cName varchar,
            eNanme varchar,
            score numeric,
            evaluation numeric,
            findInfo text,
            movieInfo text
        )
    '''
    conn = sqlite3.connect(dbPath)
    cursor = conn.cursor()
    for i in range(250):
        print("第%d条" % (i + 1))
        datalist2 = datalist[i]
        data = ""
        # 保存数据
        for j in range(8):
            if j == 7:
                data = data + '"' + datalist2[j] + '"'
            else:
                data = data + '"' + datalist2[j] + '",'
        sql = '''
            insert into movie250(
                movieLink,imgLink,cName,eNanme,score,evaluation,findInfo,movieInfo) 
            values(%s)
        ''' % data
        # print(sql)
        cursor.execute(sql)
        conn.commit()
    cursor.close()
    conn.close()


# 4.得到一个指定url的网页内容
def askURL(url):
    head = {
        "User-Agent": "Mozilla/5.0 (Linux; Android 6.0; Nexus 5 Build/MRA58N) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.81 Mobile Safari/537.36"
        # 用户代理：告诉服务器我们使用何种浏览器进或机器行访问，能接收什么样的数据
    }
    # 对请求头进行封装
    request = urllib.request.Request(url=url, headers=head)
    # 进行异常的捕捉
    try:
        response = urllib.request.urlopen(request)
        html = response.read().decode("utf-8")
        # print(html)
    except urllib.error.URLError as e:
        if hasattr(e, "code"): print(e)  # hasattr()函数表示如果对象有该属性返回 True，否则返回 False。
        if hasattr(e, "reason"): print(e)
    return html


if __name__ == "__main__":
    main()
